﻿using Kingdee.BOS;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Contracts.Report;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.Util;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DzInfo.Report.ND
{
    [Description("ND：客制化应收账龄分析表"), HotUpdate]
    public class NewAgingAnalysisService : SysReportBaseService
    {
        public override void Initialize()
        {
            base.Initialize();
            this.ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;
            this.ReportProperty.ReportName = new LocaleValue("应收账龄分析表", base.Context.UserLocale.LCID);
            this.IsCreateTempTableByPlugin = true;
            this.ReportProperty.IsUIDesignerColumns = false;
            this.ReportProperty.IsGroupSummary = true;
            this.ReportProperty.SimpleAllCols = false;
            this.ReportProperty.PrimaryKeyFieldName = "FCUSTOMERID";
            this.ReportProperty.IdentityFieldName = "FIDENTITYID";
        }
        protected override string BuilderTempTableOrderBySQL(IRptParams filter)
        {
            string sortStr = filter.FilterParameter.SortString;
            if (sortStr.Length > 0)
            {
                return sortStr;
            }
            return " FCUSTNAME ";
        }
        public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
        {
            var cusFilter = filter.FilterParameter.CustomFilter;
            string seqFld = string.Format(base.KSQL_SEQ, BuilderTempTableOrderBySQL(filter));
            string createSql = GetCreateSql();
            string dataSql = GetDataSql(cusFilter);
            string endSql = GetSql(seqFld, tableName);
            string sql = createSql + dataSql + endSql;
            DBUtils.ExecuteDynamicObject(this.Context, sql);
        }

        private string GetSql(string seqFld, string tableName)
        {
            string endSql = $" select * ,{seqFld} into {tableName} from #TEMP  ";
            return endSql;
        }

        private string GetDataSql(DynamicObject cusFilter)
        {
            var orgs = cusFilter["FSETTLEORGID"] as DynamicObjectCollection;
            var cuts = cusFilter["FCUSTOMERID"] as DynamicObjectCollection;
            var strDate = cusFilter["FSTRDate"];
            var endDate = cusFilter["FENDDate"];
            string filStr = "";
            if (orgs.Count > 0)
            {
                var oList = orgs.Select(s => Convert.ToInt32(s["FSETTLEORGID_id"]));
                var orgids = string.Join(",", oList);
                filStr += $" and T0.FSETTLEORGID in ({orgids}) ";
            }
            if (cuts.Count > 0)
            {
                var cList = cuts
                    .Select(s => (s["FCUSTOMERID"] as DynamicObject)?["name"]?.ToString())
                    .Where(name => !string.IsNullOrEmpty(name))
                    .Distinct()
                    .ToList();

                if (cList.Count > 0)
                {
                    // 转义单引号并用单引号包裹每个值
                    var escapedValues = cList.Select(s => $"'{s.Replace("'", "''")}'");
                    string cutids = string.Join(",", escapedValues);
                    filStr += $" AND T4.FNAME IN ({cutids})";
                }
            }
            if (strDate != null)
            {
                filStr += $" and T0.F_POSW_CKDate>= '{strDate}' ";
            }
            if (endDate != null)
            {
                filStr += $"  and T0.F_POSW_CKDate<= '{endDate}' ";
            }

            string dataSql = string.Format(@" /*dialect*/ 
            WITH BaseData AS (
                SELECT 
                    T0.FCUSTOMERID,
                    T0.FSETTLEORGID,
                    T2.FNAME FCURRENCYID,
                    T3.FDATAVALUE AS FXSLX,
                    CONVERT(VARCHAR(6), T0.F_POSW_CKDate, 112) AS FNY,
                    T1.FALLAMOUNTFOR
                FROM t_AR_receivable T0
                INNER JOIN t_AR_receivableEntry T1 ON T1.FID = T0.FID
	            JOIN T_BD_CURRENCY_L T2 ON T2.FCURRENCYID=T0.FCURRENCYID AND T2.FLOCALEID=2052
                JOIN T_BD_CUSTOMER_L T4 ON T4.FCUSTID=T0.FCUSTOMERID AND T4.FLOCALEID=2052
	            LEFT JOIN T_BAS_ASSISTANTDATAENTRY_L T3 ON T3.FENTRYID=T0.F_NDN_ASSISTANT1 AND T3.FLOCALEID=2052
                WHERE T0.F_POSW_CKDate IS NOT NULL 
                  AND (T1.FALLAMOUNTFOR - T1.FRECEIVEAMOUNT) <> 0  {0}
            ),
            AmountByPeriod AS (
                SELECT 
                    b1.FCUSTOMERID,
                    b1.FCURRENCYID,
                    B1.FSETTLEORGID,
                    b1.FXSLX,
                    STUFF((
                        SELECT ', ' + b2.FNY + '月:' +
                               CONVERT(VARCHAR(20), 
                               CAST(SUM(b2.FALLAMOUNTFOR)/10000 AS DECIMAL(18,2)))+'万'+b2.FCURRENCYID
                        FROM BaseData b2
                        WHERE b2.FCUSTOMERID = b1.FCUSTOMERID
                          AND b2.FCURRENCYID = b1.FCURRENCYID
                          AND b2.FXSLX = b1.FXSLX
                        GROUP BY b2.FNY,b2.FCURRENCYID
                        ORDER BY b2.FNY
                        FOR XML PATH('')
                    ), 1, 2, '') AS PeriodAmounts
                FROM (
                    SELECT DISTINCT FSETTLEORGID,FCUSTOMERID, FCURRENCYID, FXSLX
                    FROM BaseData
                ) b1
            ),
            TotalAmount AS (
                SELECT 
                    T0.FCUSTOMERID,
                    T0.FSETTLEORGID,
                    T2.FNAME FCURRENCYID,
                    T3.FDATAVALUE AS FXSLX,
                    SUM(T1.FALLAMOUNTFOR) AS TotalAmount,
		            SUM(T1.FALLAMOUNT) AS TotalAmountBW
                FROM t_AR_receivable T0
                INNER JOIN t_AR_receivableEntry T1 ON T1.FID = T0.FID
	            JOIN T_BD_CURRENCY_L T2 ON T2.FCURRENCYID=T0.FCURRENCYID AND T2.FLOCALEID=2052
                JOIN T_BD_CUSTOMER_L T4 ON T4.FCUSTID=T0.FCUSTOMERID AND T4.FLOCALEID=2052
	            LEFT JOIN T_BAS_ASSISTANTDATAENTRY_L T3 ON T3.FENTRYID=T0.F_NDN_ASSISTANT1 AND T3.FLOCALEID=2052
                WHERE T0.F_POSW_CKDate IS NOT NULL 
                  AND (T1.FALLAMOUNTFOR - T1.FRECEIVEAMOUNT) <> 0 {1}
                GROUP BY T0.FSETTLEORGID,T3.FDATAVALUE, T0.FCUSTOMERID, T2.FNAME
            )

            INSERT INTO #TEMP (FSETTLEORGID,FORGNAME,FCUSTNAME,FCUSTOMERID, FCURRENCYID, FXSLX, FTotalAmount,FTotalAmountBW, FPeriodAmounts)
            SELECT 
	            T.FSETTLEORGID,
                C.FNAME FORGNAME,
                B.FNAME FCUSTNAME,T.FCUSTOMERID,
                t.FCURRENCYID,
                t.FXSLX,
                t.TotalAmount FTotalAmount,
	            T.TotalAmountBW FTotalAmountBW,
                a.PeriodAmounts FPeriodAmounts
            FROM TotalAmount t
            INNER JOIN AmountByPeriod a 
                ON t.FCUSTOMERID = a.FCUSTOMERID
                AND t.FCURRENCYID = a.FCURRENCYID
                AND t.FXSLX = a.FXSLX
            LEFT JOIN T_BD_CUSTOMER_L B ON B.FCUSTID=T.FCUSTOMERID AND B.FLOCALEID=2052
            LEFT JOIN T_ORG_ORGANIZATIONS_L C ON C.FORGID=T.FSETTLEORGID AND C.FLOCALEID=2052
            ORDER BY B.FNAME, t.FCURRENCYID,T.FCUSTOMERID 
            ", filStr, filStr);
            return dataSql;
        }

        private string GetCreateSql()
        {
            string createSql = @"/*dialect*/ CREATE TABLE #TEMP (
            FSETTLEORGID INT NULL,
            FORGNAME VARCHAR(255) NULL,
            FCUSTNAME VARCHAR(255) NULL,
            FCUSTOMERID INT NULL,
            FCURRENCYID VARCHAR(10) NULL,
            FXSLX VARCHAR(20) NULL,
            FTotalAmount DECIMAL(38,10)  NULL,
            FTotalAmountBW DECIMAL(38,10)  NULL,
            FPeriodAmounts VARCHAR(MAX)  NULL,
            ); ";
            return createSql;
        }
    }
}
